package cool.houge.demo.repository;

import cool.houge.demo.entity.VSalarySummary;
import cool.houge.demo.entity.query.QVSalarySummary;
import io.ebean.*;

import java.util.List;

/**
 * 薪水.
 *
 * @author ZY (kzou227@qq.com)
 */
public class VSalarySummaryRepository extends BeanFinder<Integer, VSalarySummary> {

    public VSalarySummaryRepository(Database db) {
        super(VSalarySummary.class, db);
    }

    public PagedList<VSalarySummary> findPage(int offset, int limit) {
        return new QVSalarySummary(db()).setFirstRow(offset).setMaxRows(limit).findPagedList();
    }

    public List<VSalarySummary> aggregateByDepartment() {
        var qb = new QVSalarySummary(db());
        return qb.select(qb.departmentId, StdOperators.sum(qb.salary)).findList();
    }

    public List<VSalarySummary> aggregateByPayYear() {
        var sql = RawSqlBuilder.parse("select pay_year, sum(salary) as salary from v_salary_summary group by pay_year")
                .create();
        return db().find(VSalarySummary.class).setRawSql(sql).findList();
    }

    public List<VSalarySummary> aggregateByPayMonth(String payYear) {
        var sql = RawSqlBuilder.parse(
                        "select pay_month, sum(salary) as salary from v_salary_summary where pay_year=:pay_year group by pay_month")
                .create();
        return db().find(VSalarySummary.class)
                .setRawSql(sql)
                .setParameter("pay_year", payYear)
                .findList();
    }
}
